options mprint;

options ls=120 symbolgen mlogic;   

/******************************************************************    
                                                                       
this pgm reads transunion non-roll-up data into sas datasets;
                                                                       
******************************************************************/    

libname save "./" ;   


*******************;
*** HEADER FILE ***;
*******************;

/*** MarketArea, SubmarketArea, In_File_SinceDate load only as character, this is different from Shira's code and also from previous data sets;***/

%macro header;

    data save.header&sufx.;
        infile "./&tt." lrecl=2000 missover;
        input
            RecordType $ 1-2
            MatchType $ 3-3
            teditseq $ 4-15
            In_File_SinceDate $ 16-21
            Address_Discrepency $ 22-22
            PermID2010 $ 23-35
            tueditseq $ 36-49;

        if RecordType ne "HD" then delete;
    run;

    proc sort data=save.header&sufx.;
        by teditseq;
    run;
    
%mend;

%let sufx = 201006; 
%let tt =J127787_D20160830_S272510_elfDdvUnbundleOutputHeader_C2_HD_P001.dat;
%header;
run;

%let sufx = 201406; 
%let tt =J127787_D20160830_S272511_elfDdvUnbundleOutputHeader_C2_HD_P001.dat;
%header;
run;

%let sufx = 201206; 
%let tt =J127787_D20160830_S272512_elfDdvUnbundleOutputHeader_C2_HD_P001.dat;
%header;
run;

*******************;   
*** TRADE LINES ***;   
*******************;   

/*** DateOpened, DateVerified, DateReported, DateClosed, DatePaidOUt load as characters, should be numeric per Shira's code and per older data sets; 
*** Same issue for HighCredit, CurrentBalance, AmountPastDue, CurrentMOP, CreditLimit ;*/
/* There doesn't appear to be any data for variables maxdelvl, nummnths, past3059, past6089, past90up in the text file -jz 3/5/13*/

%macro trade;
    data save.trade&sufx.;
        infile "./&tt." lrecl=2000 missover;

        input
            RecordType $		1   - 2 
            MatchType		$	3   - 3
            teditseq		$	4   - 15
            subcdt		$  	17  - 24
            dtopen		 	25 - 30
            ECOA 		$	31  - 31
            hicredit 		$	32  - 38
            dtveri		 39 - 44
            dtrept		 45 - 50
            dtclose 		 51 - 56
            dtpdout		 57 - 62
            assoccd		$	63  - 63
            PayPat 		$	64  - 111
            acctbal		$	112 - 118
            amtpast		$	119 - 125
            acctype 		$	126 - 126
            currmop		$	127 - 128
            crdtlim		$	129 - 135
            Terms 		$	136 - 143
            maxdeldt 	          144 - 149
            maxdelam		$	150 - 156
            maxdelvl		$	157 - 158
            nummnths		$ 	159 - 161
            past3059		$	162 - 163
            past6089		$	164 - 165
            past90up		$	166 - 167
            loantypt 		$	168 - 169
            dispcd		$	170 - 172
            collfld		$	173 - 205;
        if RecordType ne "TR" then delete;
    run;

    proc sort data=save.trade&sufx.;
        by teditseq;
    run;
    
    data save.trade&sufx.(rename=(HiCreditN = HiCredit
        acctbalN = acctbal amtpastN = amtpast crdtlimN = crdtlim
        maxdelamN = maxdelam));
        set save.trade&sufx.;

     /*set z's to missing*/
        if HiCredit eq "ZZZZZZZ" then HiCredit = .;
        if acctbal eq "ZZZZZZZ" then acctbal = .;
        if amtpast eq "ZZZZZZZ" then amtpast = .;
        if crdtlim eq "ZZZZZZZ" then crdtlim = .;
        if maxdelam eq "ZZZZZZZ" then maxdelam =.;
        if Terms eq "ZZZZZZZZ" then Terms = "";

     /*convert back to numbers*/
        format HiCreditN acctbalN amtpastN crdtlimN MaxDelAmN 7.0;

        HiCreditN = HiCredit*1;
        acctbalN = acctbal*1;
        amtpastN = amtpast*1;
        crdtlimN = crdtlim*1;
        maxdelamN = maxdelam*1;

        drop HiCredit acctbal amtpast crdtlim maxdelam;
    run;
    
%mend;

%let sufx = 201006;
%let tt =J127787_D20160830_S272510_elfDdvUnbundleOutputAc01_C2_TR_P001.dat;
%trade;
run;

%let sufx = 201406;
%let tt =J127787_D20160830_S272511_elfDdvUnbundleOutputAc01_C2_TR_P001.dat;
%trade;
run;

%let sufx = 201206;
%let tt =J127787_D20160830_S272512_elfDdvUnbundleOutputAc01_C2_TR_P001.dat;
%trade;
run;




******************; 
*** INQUIRIES ***; 
******************; 

%macro iq;
    data save.inquiry&sufx.;

        infile "./&tt." lrecl=2000 missover;
        input
            RecordType		$1   - 2
            MatchType		$	3 -	3
            teditseq		$	4 -	15
            InquiryTypeMask			16 -	16
            SubscriberCode 		$	17 -	24
            InquiryDate		??	yymmdd8.
            
/*25 -	32*/
            
/*was commented out by kamila, dont know why*/

            ECOA			$	33 -	33
            LoanType 		$	34 -	35
            LoanAmount 		$	36 -	42;
        if RecordType ne "IQ" then delete;

        format InquiryDate  yymmdd8.;
    run;

    proc sort data=save.inquiry&sufx.;
        by teditseq;
    run;

%mend;

%let sufx = 201006; 
%let tt =J127787_D20160830_S272510_elfDdvUnbundleOutputIn01_C2_IQ_P001.dat;
%iq;
run;

%let sufx = 201406; 
%let tt =J127787_D20160830_S272511_elfDdvUnbundleOutputIn01_C2_IQ_P001.dat;
%iq;
run;

%let sufx = 201206; 
%let tt =J127787_D20160830_S272512_elfDdvUnbundleOutputIn01_C2_IQ_P001.dat;
%iq;
run;


**********************; 
*** PUBLIC RECORDS ***;  
**********************; 

%macro pr;
    data save.pubrec&sufx.;

        infile "./&tt." lrecl=2000 missover;
        input
            RecordType	$	1 -	2
            MatchType	$	3 -	3
            teditseq 	$	4 -	15
            DateReported	?? 	yymmn6.
            Amount	$	22 -	27
            PublicRecordType $	28 -	29
            DatePaid	??	yymmn6.
            ECOA		$	36 -	36
           /* Assets		$	37 -	42*/
           /* Liabilities	$	43 -	48*/
            Attorney	$	49 -	86
            Plaintiff	$	87 -	124
            DocketNumber	$	125 -	135 ;
        if RecordType ne "PR" then delete;
        format DateReported DatePaid yymmn6.;
    run;

    proc sort data=save.pubrec&sufx.;
        by teditseq;
    run;


*****refer to old code below by searching: HERE *******;


    data save.pubrec&sufx.(rename=(AmountN=Amount));
        set save.pubrec&sufx.;

    /*set z's to missing*/
        if Amount eq "ZZZZZZ" then Amount = .;

     /*convert back to numbers*/
        format  AmountN 6.0;
        
        AmountN = Amount*1;
        drop Amount;
    run;
    
%mend;



%let sufx = 201006; 
%let tt =J127787_D20160830_S272510_elfDdvUnbundleOutputPr01_C2_PR_P001.dat;
%pr;
run;

%let sufx = 201406; 
%let tt =J127787_D20160830_S272511_elfDdvUnbundleOutputPr01_C2_PR_P001.dat;
%pr;
run;

%let sufx = 201206; 
%let tt =J127787_D20160830_S272512_elfDdvUnbundleOutputPr01_C2_PR_P001.dat;
%pr;
run;



*******************;
*** COLLECTIONS ***;
*******************;
%macro co;
    data save.collect&sufx.;
        infile "./&tt." lrecl=2000 missover;

        input
            RecordType	$	1 -	2
            MatchType		$ 3 -	3
            teditseq		$ 4 -	15
            DateReported		?? yymmn6.	/*16 - 21*/
            SubscriberCode		$ 22 -	29
            AmountOwed		  30 -	35
            Status			$ 36 -	37
            DatePaid		?? yymmn6.	/*38 - 43*/
            ECOA			$ 44 -	44
            CreditorName		$ 45 -	82 ;
        if RecordType ne "CO" then delete;
        format DateReported DatePaid yymmn6.;
    run;
    
    proc sort data=save.collect&sufx.;
        by teditseq;
    run;

%mend;



%let sufx = 201006; 
%let tt =J127787_D20160830_S272510_elfDdvUnbundleOutputCo01_C2_CO_P001.dat;
%co;
run;

%let sufx = 201406; 
%let tt =J127787_D20160830_S272511_elfDdvUnbundleOutputCo01_C2_CO_P001.dat;
%co;
run;

%let sufx = 201206; 
%let tt =J127787_D20160830_S272512_elfDdvUnbundleOutputCo01_C2_CO_P001.dat;
%co;
run;


***************; 
*** SUMMARY ***; 
***************; 

%macro summary;
    data save.summary&sufx.;

        infile "./&tt." lrecl=2000 missover;

        input
            RecordType	$	1 -	2 
            MatchType	$	3 -	3
            teditseq	$	4 -	15
            Inquiries		16 - 18
            Trades			19 - 21
            Collections		22 - 24
            PublicRecords		25 - 27
            MOP01			28 - 30
            MOP02			31 - 33
            MOP03			34 - 36
            MOP04			37 - 39
            MOP05			40 - 42
            MOP07			43 - 45
            MOP08			46 - 48
            MOP09			49 - 51
            MOP00			52 - 54
            MOPUC			55 - 57
            MOPUR			58 - 60 ;
        if RecordType ne "ZZ" then delete;
        
    proc sort data=save.summary&sufx.;
        by teditseq;
    run;

    data save.summary&sufx.;
        set save.summary&sufx.;
        if recordtype=" " then delete;
    run;
    
    proc contents data = save.summary&sufx.;
    run;

    proc freq data = save.summary&sufx.;

        tables MatchType teditseq Inquiries Trades Collections
            PublicRecords MOP01 MOP02 MOP03 MOP04 MOP05 MOP07
            MOP08 MOP09 MOP00 MOPUC MOPUR;
    run;

    proc print data = save.summary&sufx. (obs = 10);
    run;

%mend;



%let sufx = 201006; 
%let tt =J127787_D20160830_S272510_elfDdvUnbundleOutputSummary_C2_ZZ_P001.dat;
%summary;
run;

%let sufx = 201406; 
%let tt =J127787_D20160830_S272511_elfDdvUnbundleOutputSummary_C2_ZZ_P001.dat;
%summary;
run;

%let sufx = 201206; 
%let tt =J127787_D20160830_S272512_elfDdvUnbundleOutputSummary_C2_ZZ_P001.dat;
%summary;
run;


endsas ; 



*******   HERE *******;   
    
    data save.pubrec&sufx.(rename=(AssetsN=Assets
        LiabilitiesN=Liabilities AmountN=Amount));
        set save.pubrec&sufx.;

    /*set z's to missing*/
        if Assets eq "ZZZZZZ" then Assets = .;
        if Liabilities eq "ZZZZZZ" then Liabilities = .;
        if Amount eq "ZZZZZZ" then Amount = .;

     /*convert back to numbers*/
        format AssetsN LiabilitiesN AmountN 6.0;
        
        AssetsN = Assets*1;
        LiabilitiesN = Liabilities*1;
        AmountN = Amount*1;
        drop Assets Liabilities Amount;
    run;
